﻿/********************************************************************************
** 作者： Mick
** 邮箱： zheng_jinfan@126.com
** 主页： http://www.zhengjinfan.cn
** 创始时间：2016-2-25
** 描述：
**      Sql查询翻译扩展类
*********************************************************************************/
using System;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using BestEasyCMS.Framework.Extension;
using BestEasyCMS.Framework.Querying;

namespace BestEasyCMS.Repository.Base
{
    /// <summary>
    /// Sql查询翻译扩展类
    /// </summary>
    public static class QueryTranslator
    {
        /// <summary>
        /// 将Query对象翻译成Sql查询语句
        /// </summary>
        /// <param name="query"></param>
        /// <param name="sqlModel"></param>
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Security", "CA2100:检查 SQL 查询是否存在安全漏洞")]
        public static void TranslateInto(this Query query, SqlModel sqlModel)
        {
            if (query.IsNamedQuery())//存储过程，只支持查询
            {
                var sqlCmd = new SqlCommand(query.ProcedureName)
                {
                    CommandType = CommandType.StoredProcedure
                };
                var sqlParams = query.Criteria.Select(criterion => new SqlParameter("@" + criterion.PropertyName, criterion.Value)).ToList();
                sqlModel.SqlCommand = sqlCmd;
                sqlModel.SqlParameters = sqlParams;
            }
            else
            {
                var sqlQuery = new StringBuilder();
                var isNotfirstFilterClause = false;
                if (query.Criteria.Any())
                    sqlQuery.Append("WHERE ");
                //初始化参数 0开始
                var paramIndex = 0;
                //实例化sql参数
                sqlModel.Args = new object[query.Criteria.Count()];
                foreach (var criterion in query.Criteria)
                {
                    if (isNotfirstFilterClause)
                        sqlQuery.Append(GetQueryOperator(query));

                    //添加查询字符串
                    if (criterion.CriteriaOperator == CriteriaOperator.In ||
                        criterion.CriteriaOperator == CriteriaOperator.NotIn)
                    {
                        sqlQuery.Append(string.Format("{0} {1} (@{2}) ", criterion.PropertyName,
                              FindSqlOperatorFor(criterion.CriteriaOperator), paramIndex));
                    }
                    else
                    {
                        sqlQuery.Append(AddFilterClauseFrom(criterion, paramIndex));
                    }

                    switch (criterion.CriteriaOperator)
                    {
                        //LIKE 需要额外处理
                        case CriteriaOperator.Like:
                            sqlModel.Args[paramIndex] = "%" + criterion.Value + "%";
                            break;
                        case CriteriaOperator.LikeLt:
                            sqlModel.Args[paramIndex] = "" + criterion.Value + "%";
                            break;
                        case CriteriaOperator.LikeRt:
                            sqlModel.Args[paramIndex] = "%" + criterion.Value + "";
                            break;
                        default:
                            //添加参数
                            sqlModel.Args[paramIndex] = criterion.Value;
                            break;
                    }

                    isNotfirstFilterClause = true;
                    paramIndex++;
                }
                //添加排序字符串
                if (query.OrderByProperty != null)
                    sqlQuery.Append(GenerateByClauseFrom(query.OrderByProperty));
                //设置查询字符串
                sqlModel.Sql = sqlQuery.ToStr();
            }
        }
        /// <summary>
        /// 获取排序字符串
        /// </summary>
        /// <param name="orderByClause"></param>
        /// <returns></returns>
        private static string GenerateByClauseFrom(OrderByClause orderByClause)
        {
            return String.Format("ORDER BY {0} {1}", orderByClause.PropertyName, orderByClause.Desc ? "DESC" : "ASC");
        }
        /// <summary>
        /// 获得查询操作符
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        private static string GetQueryOperator(Query query)
        {
            return query.QueryOperator == QueryOperator.And ? "AND " : "OR ";
        }
        /// <summary>
        /// 添加筛选器子句
        /// </summary>
        /// <param name="criterion"></param>
        /// <param name="paramIndex">参数索引</param>
        /// <returns></returns>
        private static string AddFilterClauseFrom(Criterion criterion, int paramIndex)
        {
            return string.Format("{0} {1} @{2} ", criterion.PropertyName, FindSqlOperatorFor(criterion.CriteriaOperator), paramIndex);
        }
        /// <summary>
        /// 获取SQL操作符
        /// </summary>
        /// <param name="criteriaOperator"></param>
        /// <returns></returns>
        private static string FindSqlOperatorFor(CriteriaOperator criteriaOperator)
        {
            switch (criteriaOperator)
            {
                case CriteriaOperator.Equal:
                    return "=";
                case CriteriaOperator.LesserThanOrEqual:
                    return "<=";
                case CriteriaOperator.GreaterThanOrEqual:
                    return ">=";
                case CriteriaOperator.LesserThan:
                    return "<";
                case CriteriaOperator.GreaterThan:
                    return ">";
                case CriteriaOperator.Like:
                    return "LIKE";
                case CriteriaOperator.LikeLt:
                    return "LIKE";
                case CriteriaOperator.LikeRt:
                    return "LIKE";
                case CriteriaOperator.In:
                    return "IN";
                case CriteriaOperator.NotIn:
                    return "NOT IN";
                default:
                    throw new ApplicationException("No operator defined.");
            }
        }
    }
}
